import openpyxl
from openpyxl.utils import get_column_letter

#创建新表单
wb=openpyxl.Workbook()
sheet=wb.active

#change the name of the sheet
print(sheet.title)
print(wb.sheetnames)
sheet.title='Happy2017'#给表单改名
print(wb.sheetnames)

#wb.save('example_copy.xlsx') #写入内容要保存，才能存储在磁盘上

#新建表单
wb.create_sheet(index=0,title='First Sheet')
wb.create_sheet(title='Last Sheet')#不写位置默认放最后
print(wb.sheetnames)

#删除表单对象
del wb['Last Sheet']
print(wb.sheetnames)

#保存操作
wb.save('temp1.xlsx')

#write values to cells
wb=openpyxl.Workbook()
sheet=wb.active

#直接赋值单元格
sheet['A1']='Hello Python'
print(sheet['A1'].value)

#在表单中添加数
ws1=wb.create_sheet('range names')
for row in range(1,40):
    ws1.append(range(17))

#在表单中添加列表
ws2=wb.create_sheet('List')
rows = [
	['Number', 'Batch 1', 'Batch2'],
	[2, 40, 30],
	[3, 40, 25],
	[4, 50, 30],
	[5, 30, 10],
	[6, 40, 30],
	[7, 78, 52],
]
for row in rows:
    ws2.append(row)

#通过循环给单元格赋值
ws3=wb.create_sheet(title='Data')
for row in range(5,30):
    for col in range(15,54):
        ws3.cell(column=col,row=row,value=get_column_letter(col))
print(ws3['AA10'].value)

wb.save(filename='empty_book.xlsx')
